package packageGenerateData;

import packageInputData.DbConnection;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;

public class GenerateDataZWD {
	
	/**
	 * function 
	 * to generate ZWD data and put it into the database
	 * @return			void
	*/
	@SuppressWarnings({ "rawtypes", "deprecation" })
	public static void generateZWD()
	{
		//to contain ztd data
		ArrayList <Integer> ztdDoy =new ArrayList<Integer>();
		ArrayList <Integer> ztdEpok =new ArrayList<Integer>();
		ArrayList <Double> ztdValue =new ArrayList<Double>();
		
		//to contain zhd data
		ArrayList <Integer> zhdDoy =new ArrayList<Integer>();
		ArrayList <Integer> zhdEpok =new ArrayList<Integer>();
		ArrayList <Integer> zhdHr =new ArrayList<Integer>();
		ArrayList <Integer> zhdMn =new ArrayList<Integer>();
		ArrayList <Double> zhdValue =new ArrayList<Double>();
		ArrayList <Double> zhdSigValue =new ArrayList<Double>();
			
		//to contain zwd data
		ArrayList <Integer> zwdDoy =new ArrayList<Integer>();
		ArrayList <Integer> zwdEpok =new ArrayList<Integer>();
		ArrayList <Double> zwdValue =new ArrayList<Double>();
		ArrayList <Double> zwdSigValue =new ArrayList<Double>();
		
		
		List list1 = new ArrayList();
		List list2 = new ArrayList();
		List list3 = new ArrayList();
	  	
	  	String siteName="",req="";
	  	int inserts=0;
		
	  	//connect to the database
	  	Connection conn=DbConnection.connectDB();					  			
	  	QueryRunner qRunner = new QueryRunner();	
		
		//get GPS code list from sites_gps in list1 then in data[]
	  	try {
		       list1 =(List) qRunner.query(conn,
							    		   "select code from sites_gps",
							    		   new ArrayListHandler());
	  	} catch (SQLException e2) {
		       e2.printStackTrace();
	  	}
	  	//loop gps sites
		for(int cptgps=0;cptgps<list1.size();cptgps++)
		{
			Object data[] = (Object[]) list1.get(cptgps);
			siteName=data[0].toString().toUpperCase();
			//System.out.println("Station in process: "+siteName);
			//get ztdhmf value
			//execute query
			try {
				req=	"select doy,epok,ztd from ztdhmf where site=?  and session_id= "+
						MainClass.session;
				list2 =(List) qRunner.query(conn,
							    		   req,
							    		   siteName,
							    		   new ArrayListHandler());
			} catch (SQLException e2) {
			       e2.printStackTrace();
			}
			//get the result
			if(list2.size()>0)
			{
				for(int cptztd=0;cptztd<list2.size();cptztd++)
				{
					Object data2[] = (Object[]) list2.get(cptztd);
					
					ztdDoy.add(Integer.parseInt(data2[0].toString()));
					ztdEpok.add(Integer.parseInt(data2[1].toString()));
					ztdValue.add(Double.parseDouble(data2[2].toString()));					
				}
			}
			//get zhd value
			//execute query
			try {
				req="select doy,hr,mn,zhd,sigzhd from zhd where site=?  and session_id= "+MainClass.session;
				list3 =(List) qRunner.query(conn,
							    		   req,
							    		   siteName,
							    		   new ArrayListHandler());
			} catch (SQLException e2) {
			       e2.printStackTrace();
			}
			//get the result
			if(list3.size()>0)
			{
				for(int cptzhd=0;cptzhd<list3.size();cptzhd++)
				{
					Object data3[] = (Object[]) list3.get(cptzhd);
					
					zhdDoy.add(Integer.parseInt(data3[0].toString()));
					zhdHr.add(Integer.parseInt(data3[1].toString()));
					zhdMn.add(Integer.parseInt(data3[2].toString()));
					zhdValue.add(Double.parseDouble(data3[3].toString()));
					zhdSigValue.add(Double.parseDouble(data3[4].toString()));
					zhdEpok.add((120*zhdHr.get(cptzhd))+(2*zhdMn.get(cptzhd)));
				}
				for(int i=0;i<ztdValue.size();i++)
				{
					for (int j=0;j<zhdValue.size();j++)
					{
						if (ztdEpok.get(i).equals(zhdEpok.get(j)))
						{							
							zwdValue.add(ztdValue.get(i)-zhdValue.get(j));
							zwdSigValue.add(Math.sqrt(Math.pow(zhdSigValue.get(j),2)));
							zwdEpok.add(ztdEpok.get(i));
							zwdDoy.add(ztdDoy.get(i));					
							
						}
													
					}
				}
			}
			if(!(zwdValue.isEmpty()))
			{
				for(int m=0;m< zwdValue.size();m++)
				{ 
					//prepare query
					req="INSERT INTO zwd" +
							"(session_id,doy,epok,zwd,sigzwd,site)" +				
							"VALUES (?,?,?,?,?,?)";
					//execute query
					try {
						inserts = qRunner.update(conn,req,
									MainClass.session,
									zwdDoy.get(m),
									zwdEpok.get(m),
									zwdValue.get(m),
									zwdSigValue.get(m),
									siteName);
					} catch (SQLException e) {									
						e.printStackTrace();
					}
					
		
				}
			}
			//clear lists for the next iteration
			zwdValue.clear();
			zwdSigValue.clear();
			zwdEpok.clear();
			zwdDoy.clear();
			
			ztdDoy.clear(); 
			ztdEpok.clear(); 
			ztdValue.clear(); 
			
			zhdDoy.clear(); 
			zhdEpok.clear(); 
			zhdHr.clear(); 
			zhdMn.clear(); 
			zhdValue.clear();
			zhdSigValue.clear();
			
			//end loop gps
		
		}
		//check if insertion is done properly
		if (inserts==1)
		{
				System.out.println("\tInsertion in zwd done ");
		
		}
		
		//close connection
		DbUtils.closeQuietly(conn);
	}
}
